package com.office.automate.dataaccess.impl.payrole;

import java.text.SimpleDateFormat;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import com.office.automate.dataaccess.def.payrole.MonthlyEmployeePayroleDao;
import com.office.automate.dataaccess.framework.DataAccessSessionFactory;
import com.office.automate.exception.framework.FrontierVisibleException;
import com.office.automate.exception.leave.EmployeeLeaveExceptionKeys;
import com.office.automate.model.payrole.MonthlyEmployeePayrole;
import com.office.automate.model.report.EmployeeSalaryDetailsRequest;

public class MonthlyEmployeePayroleDaoImplementer implements MonthlyEmployeePayroleDao {
	private SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
	private static SessionFactory sessionFactory = null;
	private Session session = null;

	static {
		sessionFactory = DataAccessSessionFactory.getSessionFactory();
	}

	public MonthlyEmployeePayroleDaoImplementer() {
		session = sessionFactory.openSession();		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MonthlyEmployeePayrole> getEmployeeSalaryDetails(EmployeeSalaryDetailsRequest anEmployeeSalaryDetailsRequest) throws FrontierVisibleException {
		List<MonthlyEmployeePayrole> monthlyEmployeePayroleList = null;
		try {
			Query queryResult = null;
			if (anEmployeeSalaryDetailsRequest.getEmployeeId() == -1) {
				String SQL_QUERY ="from MonthlyEmployeePayrole monthlyEmployeePayrole where monthlyEmployeePayrole.billingDate between to_date(:FromDate,'YYYY-MM-DD') and to_date(:ToDate,'YYYY-MM-DD') order by monthlyEmployeePayrole.billingDate desc, monthlyEmployeePayrole.employeeId asc";
				queryResult = session.createQuery(SQL_QUERY);
				queryResult.setParameter("FromDate", formatter.format(anEmployeeSalaryDetailsRequest.getFromDate()));
				queryResult.setParameter("ToDate", formatter.format(anEmployeeSalaryDetailsRequest.getToDate()));				
			} else {
				String SQL_QUERY ="from MonthlyEmployeePayrole monthlyEmployeePayrole where monthlyEmployeePayrole.employeeId= :EmployeeId and monthlyEmployeePayrole.billingDate between to_date(:FromDate,'YYYY-MM-DD') and to_date(:ToDate,'YYYY-MM-DD') order by monthlyEmployeePayrole.billingDate desc";
				queryResult = session.createQuery(SQL_QUERY);
				queryResult.setParameter("EmployeeId", anEmployeeSalaryDetailsRequest.getEmployeeId());
				queryResult.setParameter("FromDate", formatter.format(anEmployeeSalaryDetailsRequest.getFromDate()));
				queryResult.setParameter("ToDate", formatter.format(anEmployeeSalaryDetailsRequest.getToDate()));	
			}
			 monthlyEmployeePayroleList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		
		return monthlyEmployeePayroleList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MonthlyEmployeePayrole> getEmployeeSalaryDetailsByBillingPeriod(String billingPeriod) throws FrontierVisibleException {
		List<MonthlyEmployeePayrole> monthlyEmployeePayroleList = null;
		try {
			String SQL_QUERY ="from MonthlyEmployeePayrole monthlyEmployeePayrole where monthlyEmployeePayrole.billingPeriod= :billingPeriod order by monthlyEmployeePayrole.billingDate desc, monthlyEmployeePayrole.employeeId asc";
			Query queryResult = session.createQuery(SQL_QUERY);
			queryResult.setParameter("billingPeriod", billingPeriod);			
			monthlyEmployeePayroleList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		
		return monthlyEmployeePayroleList;
	}

	@Override
	public void persistEmployeeSalaryDetails(MonthlyEmployeePayrole monthlyEmployeePayrole) throws FrontierVisibleException {
		try {
			Transaction transaction = session.beginTransaction();
			session.save(monthlyEmployeePayrole);
			transaction.commit();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_ADDING_LEAVE_REQUEST, e);
		} finally {
			session.flush();
			session.close();
		}
	}
}
